# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql
from re import S


class StockDataXuweijiePipeline:
    def process_item(self, item, spider):
        print("~~~~~~~~~~~~~pipelines打印的内容 xuweijie~~~~~~~~~~~~~~~~~")
        print(item)
        return item

class StockDataXuweijieMysqlPipeline:
    # 连接数据库操作
    def open_spider(self, spider):
        print("爬虫开始执行存储数据的操作............")
        self.db = pymysql.connect(host='192.168.18.13', user='root', password='123456', database='41wwf', port=33046, charset='utf8')
        self.cursor = self.db.cursor()
        sql_create ="CREATE TABLE IF NOT EXISTS `stock`\
                    (`data_id` INT(32) PRIMARY KEY AUTO_INCREMENT,\
                    `name` VARCHAR(16) NOT NULL,\
                    `stock_code` VARCHAR(16) NOT NULL DEFAULT '0',\
                    `rank` int(10) NOT NULL,\
                    `price` FLOAT(10) UNSIGNED NOT NULL DEFAULT '0',\
                    `zhan_die_fu` VARCHAR(16) NOT NULL DEFAULT '0',\
                    `huan_shou_lv` VARCHAR(16) NOT NULL DEFAULT '0',\
                    `zijin` VARCHAR(40) NOT NULL DEFAULT '0'\
                    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;"
        self.cursor.execute(sql_create)

    def process_item(self, item, spider):
        print('========================================')
        sql_insert = "insert into stock values (null,%s,%s,%s,%s,%s,%s,%s)"
        data = [
            item['name'], 
            item['stockCode'],
            item['rank'],
            item['price'],
            item['zhangdiefu'], 
            item['huanshoulv'], 
            item['zijin']
        ]
        self.cursor.execute(sql_insert, data)
        self.db.commit()
        return item
        
    def close_spider(self, spider):
        self.cursor.close()
        self.db.close()
        print("退出爬虫。")
